package com.hkts.reportform.purchase.dao.imp;

import com.hkts.reportform.purchase.dao.PurchaseDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassNamePurchaseDaoImp
 * @Description
 * @Author yck
 * @Date2020-11-17 10:37
 * @Version V1.0
 **/
@Repository("purchaseDaoImp")
public class PurchaseDaoImp implements PurchaseDao {
    @Autowired
    private NamedParameterJdbcTemplate temple;
    @Autowired
    private JdbcTemplate jtemple;

    @Override
    public List<Map<String, Object>> selectPageInventoryByNameOrCode(String nameOrCode,String firstRow,String lastRow) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select * from ( ");
        buffer.append("SELECT cInvCode,cInvName,isnull( cInvStd, '' ) AS cInvStd, ");
        buffer.append("ROW_NUMBER () OVER ( ORDER BY [cInvCode] ASC ) AS num ");
        buffer.append("FROM Inventory WHERE cInvName LIKE '%"+nameOrCode.trim()+"%' OR cInvCode LIKE '%"+nameOrCode.trim()+"%') ");
        buffer.append("a where a.num>=:firstRow and a.num<=:lastRow ");
        Map<String, Object> map = new HashMap<>();
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> selectInventoryByNameOrCode(String nameOrCode) {
        String sql="select cInvCode,cInvName, isnull(  cInvStd , '' ) as cInvStd  from Inventory  where  cInvName like '%"+nameOrCode+"%' or cInvCode like '%"+nameOrCode+"%'";
        Map<String, Object> map = new HashMap<>();
        map.put("cInvName", nameOrCode);
        List<Map<String, Object>> maps = jtemple.queryForList(sql);
        return maps;
    }

    @Override
    public List<Map<String, Object>> selectCurrentstockByCode(String code) {
        String sql="select ConverT(FLOAT,Round( iquantity,2))  as iquantity from currentstock where cInvCode=:code";
        Map<String, Object> map = new HashMap<>();
        map.put("code", code);
        List<Map<String, Object>> maps = temple.queryForList(sql, map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> inventoryPriceByCode(String code) {
        String sql="select  CONVERT(VARCHAR(100),r.dDate,23)  as dDate,s.iOriCost from RdRecord01 r inner join rdrecords01 s on r.ID=s.ID where cInvCode =:code and cInvCode not like '01%' and cInvCode not like '02%'  and cInvCode not like '03%'";
        Map<String, Object> map = new HashMap<>();
        map.put("code", code);
        List<Map<String, Object>> maps = temple.queryForList(sql, map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> getOrderCountByDate(String beginDate, String endDate) {
        String sql="select count(*) count,CONVERT(VARCHAR(100),cAuditDate,23) date from PO_Pomain  PO_Pomain INNER JOIN PO_Podetails ON PO_Pomain.POID= PO_Podetails.POID  where cAuditDate >= :beginDate and cAuditDate<=:endDate  and isnull(  cVerifier , '' ) <> '' GROUP BY cAuditDate";
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(sql, map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> getCountOrderExecution(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select count(*) as count from PO_Pomain m INNER JOIN PO_Podetails d ON m.POID= d.POID ");
        buffer.append("where m.cAuditDate>=:beginDate and m.cAuditDate<=:endDate ");
        buffer.append("and isnull(  m.cVerifier , '' ) <> ''  GROUP BY  d.POID ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
    @Override
    public List<Map<String, Object>> getPageOrderExecution(String beginDate, String endDate,String firstRow,String lastRow,String order) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select t.*,o.cPOID, CONVERT(VARCHAR(100),o.cAuditDate,23) as cAuditDate   from ( ");
        buffer.append("select a.*, ROW_NUMBER() OVER(ORDER BY [POID] "+order+") AS num from ( ");
        buffer.append("SELECT sum(isnull(d.iQuantity,0.000000)) AS iQuantity, ");
        //isnull(d.iReceivedQTY,0.000000)
        buffer.append("SUM(isnull(d.freceivedqty,0.000000)) AS freceivedqty, ");
        buffer.append("SUM(isnull(d.iReceivedQTY,0.000000)) AS iReceivedQTY, ");
        buffer.append("d.POID AS POID ");
        buffer.append("FROM PO_Pomain m ");
        buffer.append("INNER JOIN PO_Podetails d ON m.POID= d.POID ");
        buffer.append("where m.cAuditDate>=:beginDate and m.cAuditDate<=:endDate ");
        buffer.append("and isnull( m.cVerifier , '' ) <> '' GROUP BY d.POID   ");
        buffer.append(" ) as a) as t INNER JOIN PO_Pomain o on t.POID=o.POID where t.num>=:firstRow and t.num<=:lastRow ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
    @Override
    public List<Map<String, Object>> getCInvNameByPoid(String poid) {
        String sql="select cInvName from PO_Podetails p inner join Inventory i on p.cInvCode=i.cInvCode  where p.POID=:poid ";
        Map<String, Object> map = new HashMap<>();
        map.put("poid", poid);
        List<Map<String, Object>> maps = temple.queryForList(sql, map);
        return maps;
    }


}
